"""
Case Type   : 功能测试 -- gsql执行统计时间测试
Case Name   : 备机执行定义包的DDL语句,查看db_tim和子时间的误差在10%以内
Create At   : 2023-12-11
Owner       : kyeleze
Description :
    1. 修改enable_remote_execute参数值为on
    2. 备机执行定义包的DDL语句
    3. 查看每个sql的db_tim和子时间的误差
Expect      :
    1. 成功
    2. 执行成功
    3. 误差在10%以内
History     :
"""
import os
import unittest

from yat.test import Node

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

PrimarySh = CommonSH('PrimaryDbUser')


@unittest.skipIf(1 == PrimarySh.get_node_num(), '非主备环境不执行')
class DBTimeCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f"-----{os.path.basename(__file__)} start-----")
        self.user_node = Node('PrimaryDbUser')
        self.standby_sh = CommonSH('Standby1DbUser')
        self.common = Common('Standby1DbUser')
        self.constant = Constant()
        self.package_name = 'pkg_dbtime_test_077'
        self.user_name = 'u_dbtime_test_077'
        self.procedure_name = 'proc_dbtime_test_077'
        self.table_name = 't_dbtime_test_077'
        self.user_param = f'-U {self.user_node.db_user} ' \
                          f'-W {self.user_node.db_password}'
        self.enable_remote_execute = 'enable_remote_execute'
        self.remote_excute_default = self.common.show_param(
            self.enable_remote_execute)


    def test_sql_dbtime(self):
        step = '----step1:修改enable_remote_execute参数值为on expect:修改成功----'
        self.log.info(step)
        result = self.standby_sh.execute_gsguc(
            'reload', self.constant.GSGUC_SUCCESS_MSG,
            f'{self.enable_remote_execute}=on')
        self.assertTrue(result, '执行失败' + step)
        restart_res = self.standby_sh.restart_db_cluster()
        self.assertTrue(restart_res, '执行失败' + step)
        show_remote_excute = self.common.show_param(self.enable_remote_execute)
        self.assertEquals(show_remote_excute, 'on', '执行失败' + step)
        step = '----清理统计信息----'
        self.log.info(step)
        clean_sql = "select * from reset_unique_sql('global', 'all', 0);"
        result = self.standby_sh.execut_db_sql(clean_sql,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn('t', result.strip().splitlines()[2], '执行失败' + step)

        step = '----step2:备机执行定义包的DDL语句 expect:执行成功----'
        self.log.info(step)
        package_sql = f"create or replace package {self.package_name} is " \
                      f"var1 int:=1;" \
                      f"var2 int:=2;" \
                      f"procedure {self.procedure_name}();" \
                      f"end {self.package_name};"
        result = self.standby_sh.execut_db_sql(package_sql,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertEqual(self.constant.create_package_success_msg, result,
                         '执行失败' + step)
        body_sql = f"create  or replace package body {self.package_name} is " \
                   f"procedure {self.procedure_name}()" \
                   f"is " \
                   f"begin " \
                   f"    drop table if exists {self.table_name};" \
                   f"    create table {self.table_name}(c_int int);" \
                   f"    insert into {self.table_name} values(var1),(var2);" \
                   f"end;" \
                   f"end {self.package_name};"
        result = self.standby_sh.execut_db_sql(body_sql,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertEqual(self.constant.create_package_body_success_msg, result,
                         '执行失败' + step)
        ddl_sql = f"drop user if exists {self.user_name};" \
                  f"create user {self.user_name} password 'testing@123';" \
                  f"alter package {self.package_name} owner to " \
                  f"{self.user_name} ;" \
                  f"drop package body {self.package_name};" \
                  f"drop package {self.package_name};" \
                  f"drop user {self.user_name};"
        result = self.standby_sh.execut_db_sql(ddl_sql,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertEqual(2, result.count(self.constant.DROP_ROLE_SUCCESS_MSG),
                         '执行失败' + step)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, result,
                      '执行失败' + step)
        self.assertIn(self.constant.alter_package_success_msg, result,
                      '执行失败' + step)
        self.assertIn(self.constant.drop_package_body_success_msg, result,
                      '执行失败' + step)
        self.assertIn(self.constant.drop_package_success_msg, result,
                      '执行失败' + step)

        step = '--step3:查看每个sql的db_tim和子时间的误差 expect:误差在10%以内--'
        self.log.info(step)
        sql_list = [f'create or replace package {self.package_name}',
                    f'create  or replace package body {self.package_name}',
                    f'create user {self.user_name}',
                    f'call {self.package_name}.{self.procedure_name}()',
                    f'drop package body {self.package_name}']
        for sql in sql_list:
            check_sql = f"select (select round((db_time-total_time)/db_time," \
                        f"2) from (select db_time, sum(execution_time + " \
                        f"parse_time + plan_time + rewrite_time + " \
                        f"pl_execution_time + pl_compilation_time + " \
                        f"data_io_time + net_send_time + srt1_q + " \
                        f"srt2_simple_query + srt3_analyze_rewrite + " \
                        f"srt4_plan_query + srt5_light_query + srt6_p + " \
                        f"srt7_b + srt8_e + srt9_d + srt10_s + srt11_c + " \
                        f"srt12_u + srt13_before_query + srt14_after_query) " \
                        f"as total_time from get_instr_unique_sql() where " \
                        f"query like '{sql}%' group by db_time)) < 0.1 as res;"
            result = self.standby_sh.execut_db_sql(check_sql,
                                                   sql_type=self.user_param)
            self.log.info(result)
            self.assertIn('', result.strip().splitlines()[2],
                          '执行失败' + step)

    def tearDown(self):
        step = "-----恢复参数;expect:恢复参数成功-----"
        self.log.info(step)
        result1 = self.standby_sh.execute_gsguc(
            'reload', self.constant.GSGUC_SUCCESS_MSG,
            f'{self.enable_remote_execute}={self.remote_excute_default}')
        restart_res = self.standby_sh.restart_db_cluster()
        show_remote_excute = self.common.show_param(self.enable_remote_execute)
        self.assertTrue(result1, '执行失败' + step)
        self.assertTrue(restart_res, '执行失败' + step)
        self.assertEquals(show_remote_excute, self.remote_excute_default,
                          '执行失败' + step)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')

